FTD Over Time Case Study

Goal: Provide information about First Time Deposits (FTD) over time on client level.

FTD is calculated as the first deposit a client made across all of their logins.

import pandas as pd
import numpy as np

import plotly.express as px
import plotly.graph_objects as go
from plotly.subplots import make_subplots

import plotly.io as pio
pio.renderers.default = "notebook"

daily_ftd = pd.read_csv('./../../data/report/daily_ftd.csv', index_col=0, parse_dates=['ftd_date'])
monthly_ftd = pd.read_csv('./../../data/report/monthly_ftd.csv', index_col=0)
forecast_ftd = pd.read_csv('./../../data/report/forecast_daily_ftd.csv', index_col=0, parse_dates=['ftd_date'])

1. FTD Key Metrics (Live Registrations Only)

Metric	Value
Total FTDs	4,867
Total FTD Volume	$2,711,437 USD
Average FTD	$557 USD
Median FTD	$316 USD
FTD Conversion Rate	91.4% (4,867 / 5,325 clients)

FTD amounts are right-skewed (mean >> median), indicating a small number of high-value first deposits pulling the average up. Only Full and Light registrations are included.

2. FTD Over Time - Daily

fig = make_subplots(rows=2, cols=1, subplot_titles=('Daily FTD Count', 'Daily FTD Volume (USD)'))

fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['count'], 
                         mode='lines', name='Count'), row=1, col=1)
fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['volume'], 
                         mode='lines', name='Volume'), row=2, col=1)

fig.update_layout(height=500, showlegend=False)
fig.update_yaxes(title_text='Count', row=1, col=1)
fig.update_yaxes(title_text='USD', row=2, col=1)
fig.show()

Daily FTD count shows high variability with weekday/weekend patterns. Volume follows a similar shape but with more pronounced spikes from individual high-value deposits.

3. FTD Over Time - Monthly

fig = px.bar(monthly_ftd, x='ftd_month', y='count',
             title='Monthly FTD Count',
             labels={'ftd_month': 'Month', 'count': 'FTD Count'})
fig.update_layout(height=400)
fig.show()

fig = px.bar(monthly_ftd, x='ftd_month', y='volume',
             title='Monthly Volume FTD Amount (USD)',
             labels={'ftd_month': 'Month', 'volume': 'Volume (USD)'})
fig.update_layout(height=400)
fig.show()

display(monthly_ftd[['ftd_month', 'count', 'volume', 'avg_amount']])

	ftd_month	count	volume	avg_amount
0	2025-01	876	487815.769037	556.867316
1	2025-02	666	429361.021236	644.686218
2	2025-03	358	230712.599879	644.448603
3	2025-04	402	299541.733613	745.128691
4	2025-05	357	252317.334185	706.771244
5	2025-06	370	158618.551086	428.698787
6	2025-07	252	124290.961312	493.218100
7	2025-08	756	291496.712857	385.577663
8	2025-09	513	247469.630934	482.396941
9	2025-10	211	129346.309601	613.015685
10	2025-11	106	60465.904713	570.433063

Monthly FTD count is highest in January (876) and August (756). Later months show fewer FTDs as the observation window narrows (data ends Nov 2025). Average FTD amount fluctuates between $386–$745/month with no clear trend.

4. FTD by Registration Type

Type	FTD Count	Avg FTD (USD)	Share
Full	4,504	$574	92.5%
Light	363	$344	7.5%

Full registrations account for the vast majority of FTDs with a higher average deposit. Light registrations have lower average FTD amounts.

5. FTD Forecast (Next 30 Days)

fig = make_subplots(rows=2, cols=1, subplot_titles=('FTD Count: Actual + Forecast', 'FTD Volume: Actual + Forecast'))

fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['count'], 
                         mode='lines', name='Actual'), row=1, col=1)
fig.add_trace(go.Scatter(x=forecast_ftd['ftd_date'], y=forecast_ftd['count'], 
                         mode='lines', name='Forecast', line=dict(dash='dash', color='red')), row=1, col=1)

fig.add_trace(go.Scatter(x=daily_ftd['ftd_date'], y=daily_ftd['volume'], 
                         mode='lines', name='Actual', showlegend=False), row=2, col=1)
fig.add_trace(go.Scatter(x=forecast_ftd['ftd_date'], y=forecast_ftd['volume'], 
                         mode='lines', name='Forecast', showlegend=False, line=dict(dash='dash', color='red')), row=2, col=1)

fig.update_layout(height=500)
fig.update_yaxes(title_text='Count', row=1, col=1)
fig.update_yaxes(title_text='USD', row=2, col=1)
fig.show()

display(f'30-day forecast: {forecast_ftd["count"].sum():.0f} FTDs, ${forecast_ftd["volume"].sum():,.0f} volume')

'30-day forecast: 35 FTDs, $19,761 volume'

Moving average models were compared (windows 3, 5, 7, 14, 21). MA(3) performed best with MAE = 3.00 and RMSE = 3.54. The MA(3) rolling forecast for the next 30 days converges to ~1.2 FTDs/day (~$650/day), producing a conservative estimate of 35 FTDs and $19,761 in total volume for December 2025. The convergence is an inherent limitation of moving average forecasting - it cannot capture trends or seasonality.

6. Additional Findings

• Account structure: 74% of clients have a single trading account; average is 1.47 logins per client.
• Total transaction volume: $4.55M USD across 7,763 transactions (including non-FTD deposits).
• Temporal patterns: Transaction activity is concentrated on weekdays with a notable drop on weekends.
• Data quality: After cleaning, referential integrity holds across all tables. Transaction amounts were converted to USD using ECB historical rates.

7. Limitations

• Data covers Jan–Nov 2025 only; later months have fewer observations.
• Demo registrations were excluded; only live registrations (Full, Light) are analyzed.
• MA forecasts converge to a flat value and cannot model trends or seasonality. More advanced methods (ARIMA, Prophet) would be needed for longer-horizon predictions.
• All amounts converted to USD using ECB rates — minor discrepancies possible for exotic currencies.